package com.ruoyi.system.service.impl;

import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.utils.Mysql;
import com.ruoyi.system.domain.*;
import com.ruoyi.system.mapper.TOperationInfoMapper;
import com.ruoyi.system.service.TOperationService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class TOperationServiceImpl implements TOperationService {
    @Autowired
    TOperationInfoMapper tOperationInfoMapper;

    @Override
    public List<TOperationInfo> getUserInfo() {

        return tOperationInfoMapper.getUserInfo();
    }

    @Override
    public List<TOperationInfo> getDrugInfo() {
        return tOperationInfoMapper.getDrugInfo();
    }

    @Override
    public List<TDoctor> getDoctorSalesData(TOperationQuery tOperationQuery) {
        return tOperationInfoMapper.getDoctorSalesData(tOperationQuery);
    }

    @Override
    public TSysUser getUserDepartment(Tuserid tuserid) {
        return tOperationInfoMapper.getUserDepartment(tuserid);
    }

    @Override
    public Tres getNurseSalesData(TSalesAndRoles tSalesAndRoles) {
        return tOperationInfoMapper.getNurseSalesData(tSalesAndRoles);
    }

    /**
     * 查询护士注射次数
     *
     * @param tSalesAndRoles
     * @return
     */
    @Override
    public Tres getNurseZhusheData(TSalesAndRoles tSalesAndRoles) {
        return tOperationInfoMapper.getNurseZhusheData(tSalesAndRoles);
    }

    @Override
    public Tres getSalesAndRoles(TSalesAndRoles tSalesAndRoles) {
        return tOperationInfoMapper.getSalesAndRoles(tSalesAndRoles);
    }

    /**
     * 获取日报总营业额
     */
    @Override
    public int getDailyBusiness(TOperationQuery tOperationQuery) {
        int amount = 0;
        Connection conn = Mysql.getConn();
        Statement stmt = null;
        String startDate = tOperationQuery.getStartDate();
        String endDate = tOperationQuery.getEndDate();
        try {
            // 执行查询
            stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            if (startDate != null && startDate.length() != 0){
                sb.append(" and DATE(t.crtime)>= '"+startDate+"'");
            }else {
                sb.append(" and DATE(t.crtime)=DATE(NOW())");
            }

            if (endDate != null && endDate.length() != 0){
                sb.append(" and DATE(t.crtime)<= '"+endDate+"'");
            }
            String sql;
            //查询总销售额
            sql = "SELECT SUM(drug_price*(counts*days-refund_counts)) AS amount FROM" +
                    "   (" +
                    "      SELECT" +
                    "           t1.drug_price,t1.counts,t1.`days`,t1.`drug_status`,t1.`refund_counts`,t.crtime" +
                    "      FROM" +
                    "           t_prescription_records t" +
                    "           JOIN" +
                    "           t_prescription_drug_info t1" +
                    "           ON t.id=t1.prescriptionid" +
                    "           WHERE t.is_delete=0" + sb.toString() +
                    "    ) a";

            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                // 通过字段检索
                amount = rs.getInt("amount");
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return amount;
    }

    /**
     * 获取日报总退款额
     */
    @Override
    public int getDailyRefund(TOperationQuery tOperationQuery) {
        int refund = 0;
        Connection conn = Mysql.getConn();
        Statement stmt = null;
        String startDate = tOperationQuery.getStartDate();
        String endDate = tOperationQuery.getEndDate();
        try {
            // 执行查询
            stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            if (startDate != null && startDate.length() != 0){
                sb.append(" and DATE(refund_time)>='"+startDate+"' ");
            }else {
                sb.append(" and DATE(refund_time)=DATE(NOW()) AND prescriptionid IN ( SELECT id FROM t_prescription_records WHERE DATE(crtime)!=DATE(NOW()) GROUP BY id )");
            }

            if (endDate != null && endDate.length() != 0){
                sb.append(" and DATE(refund_time)<='"+endDate+"' AND prescriptionid IN ( SELECT id FROM t_prescription_records WHERE DATE(crtime)!="+endDate+" GROUP BY id ) ");
            }
            String sql;
            //查询总销售额
            sql = " SELECT SUM(drug_price * refund_counts) AS amount" +
                    " FROM t_prescription_drug_info" +
                    " WHERE drug_status=2"+sb.toString()
            ;
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                // 通过字段检索
                refund = rs.getInt("amount");
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return refund;
    }

    /**
     * 营业额列表
     * @param tOperationQuery
     * @return
     */
    @Override
    public  List<Map<String,Object>> getBusinessList(TOperationQuery tOperationQuery) {

        List<Map<String,Object>> list = new ArrayList<>();
        Connection conn = Mysql.getConn();
        Statement stmt = null;
        String startDate = tOperationQuery.getStartDate();
        String endDate = tOperationQuery.getEndDate();
        try {
            // 执行查询
            stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            if (startDate != null && startDate.length() != 0){
                sb.append(" and DATE(a.crtime)>='"+startDate+"'");
            }else {
                sb.append(" and DATE(a.crtime)=DATE(NOW())");
            }

            if (endDate != null && endDate.length() != 0){
                sb.append(" and DATE(a.crtime)<='"+endDate+"'");
            }
            String sql;
            //查询总销售额
            sql = "SELECT d.user_id " +
                    "  , d.user_name " +
                    "  , e.dept_name " +
                    "  , IFNULL(SUM(c.retail_price * (b.counts * b.days - IFNULL(b.refund_counts, 0))), 0) / 100 AS amount " +
                    "FROM t_prescription_records a " +
                    "  INNER JOIN t_prescription_drug_info b ON a.id = b.prescriptionid " +
                    "  INNER JOIN t_drug_info c ON b.drugid = c.id " +
                    "  INNER JOIN sys_user d ON a.doctorid = d.user_id " +
                    "  INNER JOIN sys_dept e ON d.dept_id = e.dept_id " +
                    "WHERE a.is_delete = 0 " +
                    "  AND d.dept_id != 100 AND c.drug_name != '护士处置' " + sb.toString() +
                    " GROUP BY d.user_id " +
                    "UNION " +
                    "SELECT d.user_id " +
                    "  , d.user_name " +
                    "  , e.dept_name " +
                    "  , IFNULL(SUM(c.retail_price * (b.counts * b.days - IFNULL(b.refund_counts, 0))), 0) / 100 AS amount " +
                    "FROM t_prescription_records a " +
                    "  INNER JOIN t_prescription_drug_info b ON a.id = b.prescriptionid " +
                    "  INNER JOIN t_drug_info c ON b.drugid = c.id " +
                    "  INNER JOIN sys_user d ON a.nurseid = d.user_id " +
                    "  INNER JOIN sys_dept e ON d.dept_id = e.dept_id " +
                    "WHERE a.is_delete = 0 " +
                    " AND d.dept_id != 100 AND c.drug_name = '护士处置' " + sb.toString() +
                    " GROUP BY d.user_id " +
                    "UNION " +
                    "SELECT d.user_id  " +
                    "  , d.user_name " +
                    "  , e.dept_name " +
                    " , IFNULL(SUM(c.retail_price * (b.counts * b.days - IFNULL(b.refund_counts, 0))), 0) / 100 AS amount  " +
                    "FROM t_prescription_records a  " +
                    " INNER JOIN t_prescription_drug_info b ON a.id = b.prescriptionid  " +
                    " INNER JOIN t_drug_info c ON b.drugid = c.id  " +
                    " INNER JOIN sys_user d ON a.doctorid = d.user_id  " +
                    " INNER JOIN sys_dept e ON d.dept_id = e.dept_id  " +
                    " WHERE a.is_delete = 0  " +
                    " AND d.dept_id = 100  " + sb.toString() +
                    " GROUP BY d.user_id";

            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                Map<String,Object> map = new HashMap<>();
                // 通过字段检索
                String userName = rs.getString("user_name");
                String deptName = rs.getString("dept_name");
                long amount = rs.getLong("amount");
                map.put("user",userName);
                map.put("role",deptName);
                map.put("money ",amount);
                list.add(map);
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    /**
     * 日报进货列表
     * @param tOperationQuery
     * @return
     */
    @Override
    public List<Map<String, Object>> getPurchaseList(TOperationQuery tOperationQuery) {

        List<Map<String,Object>> list = new ArrayList<>();
        Connection conn = Mysql.getConn();
        Statement stmt = null;
        String startDate = tOperationQuery.getStartDate();
        String endDate = tOperationQuery.getEndDate();
        try {
            // 执行查询
            stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            if (startDate != null && startDate.length() != 0){
                sb.append(" and DATE(t1.create_time)>='"+startDate+"'");
            }else {
                sb.append(" and DATE(t1.create_time)=DATE(NOW())");
            }

            if (endDate != null && endDate.length() != 0){
                sb.append(" and DATE(t1.create_time)<='"+endDate+"'");
            }
            String sql;
            //查询总销售额
            sql = " SELECT    " +
                    " t.drug_name drug_name,t.drug_specs drug_specs,t.unit_price/100 AS unit_price,t1.insert_num insert_num,t.unit_price/100*t1.insert_num AS money   " +
                    " FROM   " +
                    " t_drug_info t    " +
                    " JOIN    " +
                    " t_warehouse_manager t1 ON t.`id`=t1.`drug_id`   " +
                    " WHERE  1=1 "+sb.toString();

            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                Map<String,Object> map = new HashMap<>();
                // 通过字段检索
                String drugName = rs.getString("drug_name");
                String drugSpecs = rs.getString("drug_specs");
                long unitPrice = rs.getLong("unit_price");
                int num = rs.getInt("insert_num");
                double money = rs.getDouble("money");
                map.put("drugName",drugName);
                map.put("drugSpecs",drugSpecs);
                map.put("unitPrice",unitPrice);
                map.put("num",num);
                map.put("money",money);
                list.add(map);
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return list;

    }

    /**
     * 日报销售列表
     * @param tOperationQuery
     * @return
     */
    @Override
    public List<Map<String, Object>> getSaleList(TOperationQuery tOperationQuery) {

        List<Map<String,Object>> list = new ArrayList<>();
        Connection conn = Mysql.getConn();
        Statement stmt = null;
        String startDate = tOperationQuery.getStartDate();
        String endDate = tOperationQuery.getEndDate();
        try {
            // 执行查询
            stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            if (startDate != null && startDate.length() != 0){
                sb.append(" and DATE(t1.crtime)>='"+startDate+"'");
            }else {
                sb.append(" and DATE(t1.crtime)=DATE(NOW())");
            }

            if (endDate != null && endDate.length() != 0){
                sb.append(" and DATE(t1.crtime)<='"+endDate+"'");
            }
            String sql;
            //查询总销售额
            sql = "SELECT  " +
                    "  drug_name,drug_specs,drug_price, " +
                    "  SUM(counts*days-refund_counts) AS num, " +
                    "  SUM(drug_price*(counts*days-refund_counts)) AS money " +
                    "FROM " +
                    "  t_prescription_drug_info t " +
                    "  INNER JOIN " +
                    "  t_prescription_records t1 ON t1.id=t.prescriptionid " +
                    "WHERE 1=1 " + sb.toString() +
                    "GROUP BY drug_name,drug_specs,drug_price";

            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                Map<String,Object> map = new HashMap<>();
                // 通过字段检索
                String drugName = rs.getString("drug_name");
                String drugSpecs = rs.getString("drug_specs");
                long drugPrice = rs.getLong("drug_price");
                int num = rs.getInt("num");
                double money = rs.getDouble("money");
                map.put("drugName",drugName);
                map.put("drugSpecs",drugSpecs);
                map.put("unitPrice",drugPrice);
                map.put("num",num);
                map.put("money",money);
                list.add(map);
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    /**
     * 日报总进货额
     * @param tOperationQuery
     * @return
     */
    @Override
    public int getPurchaseAmount(TOperationQuery tOperationQuery) {
        int purchaseAmount = 0;
        Connection conn = Mysql.getConn();
        Statement stmt = null;
        String startDate = tOperationQuery.getStartDate();
        String endDate = tOperationQuery.getEndDate();
        try {
            // 执行查询
            stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            if (startDate != null && startDate.length() != 0){
                sb.append(" and DATE(t1.create_time)>= '"+startDate+"'");
            }else {
                sb.append(" and DATE(t1.create_time)=DATE(NOW())");
            }

            if (endDate != null && endDate.length() != 0){
                sb.append(" and DATE(t1.create_time)<= '"+endDate+"'");
            }
            String sql;
            //查询总销售额
            sql = " SELECT SUM(t.unit_price*t1.insert_num)/100 as purchase_amount" +
                    " FROM t_drug_info t" +
                    " JOIN t_warehouse_manager t1 ON t.id = t1.drug_id" +
                    " WHERE  1=1 " + sb.toString();

            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                // 通过字段检索
                purchaseAmount = rs.getInt("purchase_amount");
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return purchaseAmount;

    }


    /**
     * 获取月报总营业额
     */
    @Override
    public int getMonthBusiness(TOperationQuery tOperationQuery) {
        int amount = 0;
        Connection conn = Mysql.getConn();
        Statement stmt = null;
        String startDate = tOperationQuery.getStartDate();
        String endDate = tOperationQuery.getEndDate();
        try {
            // 执行查询
            stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            if (startDate != null && startDate.length() != 0){
                sb.append(" and DATE(t.crtime)>= '"+startDate+"'");
            }else {
                sb.append(" and DATE_FORMAT( t.crtime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");
            }

            if (endDate != null && endDate.length() != 0){
                sb.append(" and DATE(t.crtime)<= '"+endDate+"'");
            }
            String sql;
            //查询总销售额
            sql = "SELECT SUM(drug_price*(counts*days-refund_counts)) AS amount FROM" +
                    "   (" +
                    "      SELECT" +
                    "           t1.drug_price,t1.counts,t1.`days`,t1.`drug_status`,t1.`refund_counts`,t.crtime" +
                    "      FROM" +
                    "           t_prescription_records t" +
                    "           JOIN" +
                    "           t_prescription_drug_info t1" +
                    "           ON t.id=t1.prescriptionid" +
                    "           WHERE t.is_delete=0" + sb.toString() +
                    "    ) a";

            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                // 通过字段检索
                amount = rs.getInt("amount");
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return amount;
    }

    /**
     * 月报总进货额
     * @param tOperationQuery
     * @return
     */
    @Override
    public int getMonthPurchaseAmount(TOperationQuery tOperationQuery) {
        int purchaseAmount = 0;
        Connection conn = Mysql.getConn();
        Statement stmt = null;
        String startDate = tOperationQuery.getStartDate();
        String endDate = tOperationQuery.getEndDate();
        try {
            // 执行查询
            stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            if (startDate != null && startDate.length() != 0){
                sb.append(" and DATE(t1.create_time)>= '"+startDate+"'");
            }else {
                sb.append(" and DATE_FORMAT( t1.create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");
            }

            if (endDate != null && endDate.length() != 0){
                sb.append(" and DATE(t1.create_time)<= '"+endDate+"'");
            }
            String sql;
            sql = " SELECT SUM(t.unit_price*t1.insert_num)/100 as purchase_amount" +
                    " FROM t_drug_info t" +
                    " JOIN t_warehouse_manager t1 ON t.id = t1.drug_id" +
                    " WHERE  1=1 " + sb.toString() ;

            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                // 通过字段检索
                purchaseAmount = rs.getInt("purchase_amount");
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return purchaseAmount;

    }

    /**
     * 进价总额
     * @return
     */
    @Override
    public int getTotalPurchasePrice() {
        int totalPurchasePrice = 0;
        Connection conn = Mysql.getConn();
        Statement stmt = null;

        try {
            // 执行查询
            stmt = conn.createStatement();
            String sql;
            sql = " SELECT SUM(stock*unit_price)/100 as total_purchase_price" +
                    " FROM t_drug_info" +
                    " WHERE  1=1 " ;

            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                // 通过字段检索
                totalPurchasePrice = rs.getInt("total_purchase_price");
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return totalPurchasePrice;
    }

    /**
     * 销售总额
     * @return
     */
    @Override
    public int getTotalSellingPrice() {
        int totalSellingPrice = 0;
        Connection conn = Mysql.getConn();
        Statement stmt = null;

        try {
            // 执行查询
            stmt = conn.createStatement();
            String sql;
            sql = " SELECT SUM(stock*retail_price)/100 as total_selling_price" +
                    " FROM t_drug_info" +
                    " WHERE  1=1 " ;

            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                // 通过字段检索
                totalSellingPrice = rs.getInt("total_selling_price");
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return totalSellingPrice;
    }


    /**
     * 营业额列表
     * @param tOperationQuery
     * @return
     */
    @Override
    public  List<Map<String,Object>> getMonthBusinessList(TOperationQuery tOperationQuery) {

        List<Map<String,Object>> list = new ArrayList<>();
        Connection conn = Mysql.getConn();
        Statement stmt = null;
        String startDate = tOperationQuery.getStartDate();
        String endDate = tOperationQuery.getEndDate();
        try {
            // 执行查询
            stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            if (startDate != null && startDate.length() != 0){
                sb.append(" and DATE(a.crtime)>='"+startDate+"'");
            }else {
                sb.append("  and DATE_FORMAT( a.crtime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");
            }

            if (endDate != null && endDate.length() != 0){
                sb.append(" and DATE(a.crtime)<='"+endDate+"'");
            }
            String sql;
            //查询总销售额
            sql = "SELECT d.user_id  " +
                    "  , d.user_name " +
                    "  , e.dept_name " +
                    " , IFNULL(SUM(b.drug_price * (b.counts * b.days - IFNULL(b.refund_counts, 0))), 0) AS amount  " +
                    "FROM t_prescription_records a  " +
                    " INNER JOIN t_prescription_drug_info b ON a.id = b.prescriptionid  " +
                    " INNER JOIN t_drug_info c ON b.drugid = c.id  " +
                    " INNER JOIN sys_user d ON a.doctorid = d.user_id  " +
                    " INNER JOIN sys_dept e ON d.dept_id = e.dept_id  " +
                    " WHERE a.is_delete = 0  " +
                    " AND c.drug_name != '护士处置'  " +
                    " AND d.dept_id != 100  " + sb.toString() +
                    "GROUP BY d.user_id  " +
                    "UNION  " +
                    "SELECT d.user_id  " +
                    "  , d.user_name " +
                    "  , e.dept_name " +
                    " , IFNULL(SUM(b.drug_price * (b.counts * b.days - IFNULL(b.refund_counts, 0))), 0) AS amount  " +
                    "FROM t_prescription_records a  " +
                    " INNER JOIN t_prescription_drug_info b ON a.id = b.prescriptionid  " +
                    " INNER JOIN t_drug_info c ON b.drugid = c.id  " +
                    " INNER JOIN sys_user d ON a.nurseid = d.user_id  " +
                    " INNER JOIN sys_dept e ON d.dept_id = e.dept_id  " +
                    " WHERE a.is_delete = 0  " +
                    " AND c.drug_name = '护士处置'  " +
                    " AND d.dept_id != 100  " + sb.toString() +
                    "GROUP BY d.user_id  " +
                    "UNION  " +
                    "SELECT d.user_id  " +
                    "  , d.user_name " +
                    "  , e.dept_name " +
                    " , IFNULL(SUM(b.drug_price * (b.counts * b.days - IFNULL(b.refund_counts, 0))), 0) AS amount  " +
                    "FROM t_prescription_records a  " +
                    " INNER JOIN t_prescription_drug_info b ON a.id = b.prescriptionid  " +
                    " INNER JOIN t_drug_info c ON b.drugid = c.id  " +
                    " INNER JOIN sys_user d ON a.doctorid = d.user_id  " +
                    " INNER JOIN sys_dept e ON d.dept_id = e.dept_id  " +
                    " WHERE a.is_delete = 0  " +
                    " AND d.dept_id = 100  " + sb.toString() +
                    "GROUP BY d.user_id";

            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                Map<String,Object> map = new HashMap<>();
                // 通过字段检索
                String userName = rs.getString("user_name");
                String deptName = rs.getString("dept_name");
                long amount = rs.getLong("amount");
                map.put("user",userName);
                map.put("role",deptName);
                map.put("money ",amount);
                list.add(map);
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    /**
     * 获取月报总利润
     */
    @Override
    public int getProfit(TOperationQuery tOperationQuery) {
        int amount = 0;
        Connection conn = Mysql.getConn();
        Statement stmt = null;
        String startDate = tOperationQuery.getStartDate();
        String endDate = tOperationQuery.getEndDate();
        try {
            // 执行查询
            stmt = conn.createStatement();
            StringBuffer sb = new StringBuffer();
            if (startDate != null && startDate.length() != 0){
                sb.append(" and DATE(t.crtime)>= '"+startDate+"'");
            }else {
                sb.append(" and DATE_FORMAT( t.crtime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )");
            }

            if (endDate != null && endDate.length() != 0){
                sb.append(" and DATE(t.crtime)<= '"+endDate+"'");
            }
            String sql;
            sql = " SELECT SUM((drug_price-unit_price/100) * (counts * days - refund_counts)) AS amount " +
                    " FROM (  " +
                    "   SELECT t1.drug_price, t1.counts, t1.`days`, t1.`drug_status`, t1.`refund_counts`,t2.unit_price " +
                    "      , t.crtime    " +
                    "   FROM t_prescription_records t  " +
                    "      JOIN t_prescription_drug_info t1 ON t.id = t1.prescriptionid  " +
                    "      JOIN t_drug_info t2 ON t1.drugid=t2.id  " +
                    "   WHERE t.is_delete = 0  " + sb.toString() +
                    ") a";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
                amount = rs.getInt("amount");
            }
            rs.close();
        }  catch (Exception e) {
            throw new RuntimeException(e);
        }
        return amount;
    }

}
